from typing import List, Tuple, Optional, Union
from datetime import datetime
from datetime import timedelta
import statistics

from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.engine.base import Engine
from sqlalchemy.orm.session import Session
from sqlalchemy import extract
from sqlalchemy.orm.session import Session
from sqlalchemy.exc import NoResultFound

from utils import get_list_of_links_of_prouducts_to_parsed, calculate_average_value
from scraper.utils import ProductData, ReviewData
from .models import Product, Review, PointsOfSale
import settings


def get_engine_and_session_of_db() -> Tuple[Engine, Session]:
    """ Получаем объекты session и engine для работы с базой данных """

    record_connect_to_db = '{}+{}://{}:{}@{}/{}'.format(
         settings.DIALECT_DB,
         settings.DRIVER_DB,
         settings.POSTGRES_USER,
         settings.POSTGRES_PASSWORD,
         settings.POSTGRES_HOST,
         settings.POSTGRES_NAME
    )
    engine = create_engine(record_connect_to_db)
    Session = sessionmaker(bind=engine)
    session = Session()
    return (engine, session)


def close_engine_and_session_of_db(engine: Engine, session: Session) -> None:
    """ Завершаем работу с базой данных """

    session.close()
    engine.dispose()


def add_product_reviews(
    product: Product,
    list_of_reviews: List[ReviewData]
) -> None:
    """ Добавляем отзывы к продукту """

    for review in list_of_reviews:
        new_reviews = Review(**review)
        product.reviews.append(new_reviews)


def add_points_of_sale_to_product(
    session,
    product: Product,
    list_of_points_of_sale: List[str]
) -> None:
    """ Добавляем точки продажи к продукту """

    for point_of_sale in list_of_points_of_sale:
        new_point_of_sale = PointsOfSale(
            address=point_of_sale,
            product=product
        )
        session.add(new_point_of_sale)
        session.commit()


def add_product_to_db(product: ProductData) -> None:
    """ Добавляем новый продукт в базу данных """

    def check_product_to_add(product_link: str) -> bool:
        """ Проверяем нужно ли добавлять продукт """

        nonlocal session

        list_of_products = session.query(Product) \
                            .filter(Product.link==product_link) \
                            .order_by(Product.date) \
                            .all()

        if not list_of_products: return True

        product = list_of_products[-1]
        product_add_date = product.date.strftime('%Y-%m-%d')
        current_date = datetime.now().strftime('%Y-%m-%d')
        dates_dont_match = product_add_date != current_date

        if product and dates_dont_match: return True
        else: return False


    # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

    engine, session = get_engine_and_session_of_db()

    if not check_product_to_add(product['information']['link']): return

    new_product = Product(
        **product['information'],
    )
    add_product_reviews(
        new_product,
        product['reviews']
    )
    add_points_of_sale_to_product(
        session,
        new_product,
        product['points_of_sale']['list_of_points_of_sale']
    )

    session.add(new_product)
    session.commit()
    close_engine_and_session_of_db(engine, session)


def get_product_reviews_for_date(
    session: Session,
    product_name: str,
    date: datetime
) -> Union[List[Union[int, float]], List]:
    """ Получаем отзывы продукта за определенную дату """

    try:
        product: Product = session.query(
            Product
        ).filter(
            Product.name==product_name
        ).order_by(
            Product.date
        ).all()[-1]
    except NoResultFound:
        return []

    list_of_reviews: List[Review] = session.query(
        Review.evaluation
    ).filter(
        Review.product_id==product.id,
        extract('day', Review.date) == date.day,
        extract('month', Review.date) == date.month,
        extract('year', Review.date) == date.year
    ).all()
    list_of_reviews = [review[0] for review in list_of_reviews]
    return list_of_reviews


def get_average_product_evaluation_for_day(
    session: Session,
    product_name: str,
    product_date: datetime
) -> Union[int, float]:
    """ Получаем среднюю оценку продукта за день (по отзывам клиентов) """

    evaluation_for_date: Union[List[Union[int, float]], List] = \
        get_product_reviews_for_date(session, product_name, product_date)

    daily_average: Union[int, float] = \
        calculate_average_value(evaluation_for_date, 2) if evaluation_for_date else 0

    return daily_average


def get_product_for_specified_date(
    session: Session,
    start_date: datetime,
    end_date: datetime,
    product_name: str,
) -> List[Product]:
    """ Получаем продукт за указанную дату """

    list_of_products: List[Product] = session.query(
        Product
    ).filter(
        Product.name==product_name,
        Product.date.between(start_date, end_date)
    ).all()
    return list_of_products


def get_average_points_of_sale_of_products(
    list_of_products: List[Product]
) -> Union[int, float]:
    """ Получаем среднее количество точек продаж продуктов """

    list_of_points_of_sale: List[int] = []

    for product in list_of_products:
        list_of_points_of_sale.append(
            len(product.points_of_sale.all())
        )
    if list_of_points_of_sale:
        return calculate_average_value(list_of_points_of_sale, 2)
    else:
        return 0


def get_average_points_of_sale_for_previous_week(
    session: Session,
    end_date: datetime,
    product_name: str
) -> Union[float, int]:
    """
    Получаем среднее количество точек продаж продуктов за предыдущую неделю
    """

    new_end_date: datetime = end_date - timedelta(days=7)
    start_date: datetime = new_end_date - timedelta(days=7)

    list_of_products: List[Product] = get_product_for_specified_date(
        session,
        start_date,
        new_end_date,
        product_name
    )
    return get_average_points_of_sale_of_products(list_of_products)


def get_average_points_of_sale_for_week(
    session: Session,
    end_date: datetime,
    product_name: str
) -> Union[int, float]:
    """ Получаем среднее количество точек продаж продуктов за неделю """

    start_date: datetime = end_date - timedelta(days=7)
    list_of_products: List[Product] = get_product_for_specified_date(
        session,
        start_date,
        end_date,
        product_name
    )
    return get_average_points_of_sale_of_products(list_of_products)


def get_impact_on_overvall_evaluation(
    session: Session,
    date: str,
    daily_evaluations: List[Union[int, float]]
) -> Tuple:
    """ Получаем влияние на общую оценку """

    date: datetime = datetime.strptime(date, '%d-%m-%Y')
    list_of_links_of_prouducts: List[str] = get_list_of_links_of_prouducts_to_parsed()
    list_of_evaluations: List[Product] = session.query(
        Product.raiting
    ).filter(
        Product.link.in_(list_of_links_of_prouducts),
        extract('day', Product.date) == date.day,
        extract('month', Product.date) == date.month,
        extract('year', Product.date) == date.year
    ).all()

    list_of_evaluations: List[float] = [evaluation[0] for evaluation in list_of_evaluations]
    average_of_evaluations = calculate_average_value(list_of_evaluations, 2)
    average_daily_evaluations = calculate_average_value(daily_evaluations, 2)
    diff: float = round(average_daily_evaluations - average_of_evaluations, 2)

    return (average_daily_evaluations, average_of_evaluations, diff)
